package com.bwie.realtime.jtp.gd_jk.mapper;

import com.bwie.realtime.jtp.gd_jk.bean.Add_cart;
import com.bwie.realtime.jtp.gd_jk.bean.Device_type;
import com.bwie.realtime.jtp.gd_jk.bean.Order;
import com.bwie.realtime.jtp.gd_jk.bean.View_sum;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SuagrMapper {
    /*

     */
//支付金额TOP50
    @Select("SELECT\n" +
            "  '2025-09-08'\n" +
            "      ,dop.product_name\n" +
            "     ,sum(if(behavior_type='view',1,0)) as view_count\n" +
            "     ,count(distinct aul.user_id) as uv\n" +
            "     ,sum(payment_amount) AS payment_amount\n" +
            "     ,count(IF(behavior_type='purchase',1,0)) as bc\n" +
            "     ,CONCAT(ROUND(COALESCE((count(distinct aul.user_id)/sum(buyer_count))*100, 0), 2), '%') AS conversion_rate\n" +
            "FROM ads_user_log aul\n" +
            "         left join dim_order_payment dop on aul.user_id=dop.user_id\n" +
            "where time_ltz>=date_sub(current_date(),7)\n" +
            "group by dop.product_name\n" +
            "ORDER BY payment_amount DESC\n" +
            "LIMIT 50;")
    @Results({
            @Result(property = "dt", column = "dt"),
            @Result(property = "product_name", column = "product_name"),
            @Result(property = "view_count", column = "view_count"),
            @Result(property = "uv", column = "uv"),
            @Result(property = "payment_amount", column = "payment_amount"),
            @Result(property = "bc", column = "bc"),
            @Result(property = "conversion_rate", column = "conversion_rate")
    })
    List<Order> getSpeedInfo();

//    加购件数TOP50
    @Select("SELECT\n" +
            "    '2025-09-08' as dt\n" +
            "     ,dop.product_name as product_name\n" +
            "     ,sum(if(behavior_type='add_cart',1,0)) as add_cart_count\n" +
            "     ,count(distinct aul.user_id) as uv\n" +
            "     ,sum(payment_amount) AS payment_amount\n" +
            "     ,sum(behavior_type='purchase',1,0) as bc\n" +
            "     ,CONCAT(ROUND(COALESCE((sum(behavior_type='purchase',1,0)/count(distinct aul.user_id)), 0), 2), '%') AS conversion_rate\n" +
            "FROM ads_user_log aul\n" +
            "         left join dim_order_payment dop on aul.user_id=dop.user_id\n" +
            "where time_ltz>=date_sub(current_date(),7)\n" +
            "group by dop.product_name\n" +
            "ORDER BY add_cart_count DESC\n" +
            "LIMIT 50;")
    @Results({
            @Result(property = "dt", column = "dt"),
            @Result(property = "product_name", column = "product_name"),
            @Result(property = "add_cart_count", column = "add_cart_count"),
            @Result(property = "uv", column = "uv"),
            @Result(property = "payment_amount", column = "payment_amount"),
            @Result(property = "bc", column = "bc"),
            @Result(property = "conversion_rate", column = "conversion_rate")
    })
    List<Add_cart> getStockInfo();
//浏览数TOP50
    @Select("SELECT\n" +
            "    '2025-09-08' as dt\n" +
            "     ,dop.product_name as product_name\n" +
            "     ,count(if(behavior_type='view',1,0)) as view_count\n" +
            "     ,count(distinct aul.user_id) as uv\n" +
            "     ,sum(payment_amount) AS payment_amount\n" +
            "     ,sum(IF(behavior_type='purchase',1,0)) as bc\n" +
            "     ,CONCAT(ROUND(COALESCE((sum(IF(behavior_type='purchase',1,0))/count(distinct aul.user_id)), 0), 2), '%') AS conversion_rate\n" +
            "FROM ads_user_log aul\n" +
            "         left join dim_order_payment dop on aul.user_id=dop.user_id\n" +
            "where time_ltz>=date_sub(current_date(),7)\n" +
            "group by dop.product_name\n" +
            "ORDER BY view_count DESC\n" +
            "LIMIT 50;")
    @Results({
            @Result(property = "dt", column = "dt"),
            @Result(property = "product_name", column = "product_name"),
            @Result(property = "view_count", column = "view_count"),
            @Result(property = "uv", column = "uv"),
            @Result(property = "payment_amount", column = "payment_amount"),
            @Result(property = "bc", column = "bc"),
            @Result(property = "conversion_rate", column = "conversion_rate"),
    })

    List<View_sum> getVisitorInfo();


    @Select("SELECT\n" +
            "    device_type as device_type,\n" +
            "    count(distinct user_id) as uv\n" +
            "FROM ads_user_log aul\n" +
            "group by device_type\n" +
            "LIMIT 50;")
    @Results({
            @Result(property = "name", column = "device_type"),
            @Result(property = "value", column = "uv"),
    })


    List<Device_type> getDeviceType();
}